package analyzeExcel;

import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Sheet;

import com.google.common.collect.HashBasedTable;
import com.google.common.collect.Lists;
import com.google.common.collect.Table;

import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

public class ReadExcel {
	public static void main(String[] args) {
		ExcelReader reader = ExcelUtil.getReader("F:/傻逼的文件/kaoqin.xls");
		List<List<Object>> readAll = reader.read();
		List<List<Object>> newAll = Lists.newArrayList(); 
		readAll.stream().forEach(list -> {  
			List<Object> backList = list.stream()
					//去掉null
					.filter(ob -> isNotBlank(ob))
					//去掉标题
					.filter(ob -> isNotThis(ob,"员工考勤记录表"))
					.collect(Collectors.toList());
			if(backList.size() > 0) 
				newAll.add(backList); 
		});
		newAll.remove(0);
		//删除制表信息
		List<Integer> reIndex = indexs("制表",newAll);  
		deleteList(newAll,reIndex); 
		
		//根据人员进行分段
		List<Integer> cutIndex = indexs("部门",newAll);  
		Table<String, String, List<List<Object>>> aTable = HashBasedTable.create();
		List<List<Object>> pList = Lists.newArrayList();
		for(int i=0,total=cutIndex.size();i<total-1;i++){
			pList = newAll.subList(cutIndex.get(i),cutIndex.get(i+1));
			aTable.put(pList.get(0).get(1).toString(),pList.get(0).get(0).toString(),pList);
		}
		pList = newAll.subList(cutIndex.get(cutIndex.size()-1),newAll.size());
		aTable.put(pList.get(0).get(1).toString(),pList.get(0).get(0).toString(),pList);
		List<List<Object>> orderAll = Lists.newArrayList(); 
		List<List<Object>> blank = Lists.newArrayList(); 
		List<Object> blankI = Lists.newArrayList();
		blankI.add("分割======");
		blank.add(blankI);
		for(String col : aTable.columnKeySet()){
			for(String row : aTable.rowKeySet()){
				List<List<Object>> pInfo = aTable.get(row, col);
				if(pInfo != null && pInfo.size()>0){
					List<List<Object>> headWay = aTable.get(row, col);  
					orderAll.add(headWay.get(0));
					orderAll.addAll(dkTime(headWay.subList(1, headWay.size()-1)));
					orderAll.add(headWay.get(headWay.size()-1));
					//orderAll.addAll(blank);
				}
			}
		} 
		String filePath = "F:/傻逼的文件/writeTest.xls";
		FileUtil.del(filePath);
		ExcelWriter writer = ExcelUtil.getWriter(filePath);
		Sheet sheet = writer.getSheet();
		sheet.setDefaultColumnWidth(20);
		
		Font font = writer.createFont();
		font.setBold(true);
		 
		//font.setItalic(true); 
		//第二个参数表示是否忽略头部样式
		writer.getStyleSet().setFont(font, true);
		
		writer.write(orderAll);
		//关闭writer，释放内存
		writer.close();
	}
	
	public static List<List<Object>> dkTime(List<List<Object>> dkRecord){
		List<List<Object>> dkUpTime = Lists.newArrayList();
		
		for(int i=0;i<dkRecord.size();i++){
			List<Object> dkCo = dkRecord.get(i);
			int dkSize = dkCo.size()/2;  
			for(int j=0;j<dkSize;j++){
				List<Object> dkTime = Lists.newArrayList();
				String getTime = dkCo.get(2*j).toString();
				
				
				dkUpTime.add(dkTime);
			} 
		}  
		return dkUpTime;
	} 
	
	public static void deleteList(List<?> list,List<Integer> index){ 
		for(int i=index.size()-1;i>=0;i--){ 
			list.remove(list.get(index.get(i)));
		}
	}
	
	public static List<Integer> indexs(String searchStr,List<List<Object>> newAll){
		List<Integer> cutIndex = Lists.newArrayList();
		for(int i=0,total=newAll.size();i<total;i++){ 
			List<Object> info = newAll.get(i); 
			if(search(searchStr,info)){ 
				cutIndex.add(i);
			}  
		}
		return cutIndex;
	}
	
	public static boolean isNotThis(Object ob,String str){
		return ob == null ? false : !str.equals(ob.toString());
	}
	
	public static boolean isNotBlank(Object ob){
		return ob != null && StringUtils.isNotBlank(ob.toString());
	}
	
	public static boolean search(String name,List<?> list){ 
		   Pattern pattern = Pattern.compile(name); 
		   for(int i=0,total=list.size(); i < total; i++){
		      Matcher matcher = pattern.matcher(list.get(i).toString());
		      if(matcher.find()){
		         return true;
		      }
	   }
	   return false;
	}
}
